The purpose of this notebook is to review hospital bed counts - with particular focus on Childrens Hospitals - and confirmed COVID-19 cases in the United States.
This notebook combines three sets of data sources extracted via REST APIs; every time you run the the notebook you have the most up to date information from these sources.
Ensure you have imported the following libraries.
If you are using conda and/or environments like Databricks, you will only need to install altair and vega_datasets libraries as the others are already included.
# Standard Libraries
import io
# External Libraries
import requests
import numpy as np
import pandas as pd
import altair as alt
from vega_datasets import data
Below are the REST API URLs for the Confirmed US cases of COVID-19 (us_confirmed), US Hospital Bed Information (us_hospitals), and Topographical information.
# cases
us_confirmed = 'https://static.usafacts.org/public/data/covid-19/covid_confirmed_usafacts.csv?_ga=2.70208592.1380466858.1585025088-337055049.1585025088'
# hospital beds
us_hospitals = 'https://opendata.arcgis.com/datasets/1044bb19da8d4dbfb6a96eb1b4ebf629_0.csv'
# topographical
topo_usa = 'https://vega.github.io/vega-datasets/data/us-10m.json'
topo_wa = 'https://raw.githubusercontent.com/deldersveld/topojson/master/countries/us-states/WA-53-washington-counties.json'
topo_king = 'https://raw.githubusercontent.com/johan/world.geo.json/master/countries/USA/WA/King.geo.json'
Reviewing COVID-19 cases by King County
result = requests.get(us_confirmed)
buffer = io.StringIO(result.content.decode('utf-8'))
df_usa = (
pd.read_csv(buffer)
.melt(id_vars=['countyFIPS', 'County Name', 'State', 'stateFIPS'])
.dropna()
.rename(columns={
'countyFIPS': 'county_fips',
'County Name': 'county',
'State': 'state',
'stateFIPS': 'state_fips',
'variable': 'date',
'value': 'confirmed',
}).astype({
'date': 'datetime64[ns]',
})
)
display(df_usa.head())
# Disable max_rows to see more data
alt.data_transformers.disable_max_rows()
counties_usa = alt.Chart(
df_usa[df_usa['state'].isin(['WA'])]
).mark_line(point=True).encode(
x='date:T',
y='confirmed:Q',
color='county:N',
tooltip=['state', 'county', 'confirmed', 'date']
).properties(
width=600,
height=480,
title='Washington State Cases by County'
)
counties_usa
Reviewing hospital bed data
df_beds = (
pd.read_csv(us_hospitals)
.rename(columns={
'X': 'longitude',
'Y': 'latitude',
'OBJECTID': 'hospital_id',
'HOSPITAL_TYPE': 'hospital_type',
'HOSPITAL_NAME': 'hospital_name',
'HQ_ADDRESS1': 'hq_address',
})
)
# childrens hospital bed capacities
display(df_beds[df_beds['hospital_type'] == 'Childrens Hospital'].head())
# Hospital Categories
[*df_beds['hospital_type'].unique()]
base geographic mapping of Washington Statepoints which geographically maps the hospitals in Washington Statebase = alt.Chart(alt.topo_feature(topo_wa, 'cb_2015_washington_county_20m')).mark_geoshape(
fill='lightgray',
stroke='blue',
).properties(
width=1000,
height=800,
).project(
type='mercator'
)
points = alt.Chart(df_beds[(df_beds['STATE_NAME'] == 'Washington')]).mark_circle(opacity=.5).encode(
longitude='longitude:Q',
latitude='latitude:Q',
stroke=alt.value('black'),
fill=alt.value('steelblue'),
size=alt.Size('NUM_ICU_BEDS:Q', title='ICU Beds'),
tooltip=[
alt.Tooltip('STATE_NAME', title='state'),
alt.Tooltip('hospital_name', title='hospital name'),
alt.Tooltip('NUM_LICENSED_BEDS', title='licensed beds'),
alt.Tooltip('NUM_STAFFED_BEDS', title='staffed beds'),
alt.Tooltip('NUM_ICU_BEDS', title='icu beds'),
alt.Tooltip('BED_UTILIZATION', title='bed utilization', format='.0%'),
alt.Tooltip('hospital_type', title='hospital type'),
],
).properties(
title='Washington State Hospitals'
)
(base + points)
base geographic mapping of King countypoints which geographically maps the hospitals in King Countybase = alt.Chart(alt.topo_feature(topo_king, 'king')).mark_geoshape(
fill='#ffffee',
stroke='blue',
).properties(
width=800,
height=600,
).project(
type='mercator'
)
points = alt.Chart(df_beds[(df_beds['COUNTY_NAME'] == 'King')]).mark_point().encode(
longitude='longitude:Q',
latitude='latitude:Q',
size=alt.Size('NUM_ICU_BEDS:Q', title='ICU Beds'),
color='hospital_type',
fill='NUM_ICU_BEDS:Q',
shape=alt.Shape('hospital_type', title='Hospital Type'),
stroke=alt.value('black'),
tooltip=[
alt.Tooltip('STATE_NAME', title='state'),
alt.Tooltip('hospital_name', title='hospital name'),
alt.Tooltip('NUM_LICENSED_BEDS', title='licensed beds'),
alt.Tooltip('NUM_STAFFED_BEDS', title='staffed beds'),
alt.Tooltip('NUM_ICU_BEDS', title='icu beds'),
alt.Tooltip('BED_UTILIZATION', title='bed utilization', format='.0%'),
alt.Tooltip('hospital_type', title='hospital type'),
],
).properties(
title='King County Hospitals'
)
(base + points)
base geographic mapping of the USpoints which geographically maps the childrens hospitalsus_states = alt.topo_feature(topo_usa, 'states')
us_counties = alt.topo_feature(topo_usa, 'counties')
date = df_usa['date'].max()
# state borders
base_states = alt.Chart(us_states).mark_geoshape().encode(
stroke=alt.value('lightgray'), fill=alt.value('white')
).properties(
width=1000,
height=800,
).project(
type='albersUsa',
)
# confirmed cases by county
base_counties = alt.Chart(us_counties).mark_geoshape().encode(
color=alt.Color('confirmed:Q', scale=alt.Scale(type='log'), title='Confirmed'),
).transform_lookup(
lookup='id',
from_=alt.LookupData(df_usa[(df_usa['date'] == date) & (df_usa['confirmed'] > 0)], 'county_fips', ['confirmed'])
)
points = alt.Chart(df_beds[df_beds['hospital_type'] == 'Childrens Hospital']).mark_point(opacity=0.75).encode(
longitude='longitude:Q',
latitude='latitude:Q',
size=alt.Size('sum(NUM_ICU_BEDS):Q', title='ICU Beds'),
fill=alt.Fill('sum(bed_utilization):Q', title='Bed Utilization'),
stroke=alt.value('black'),
tooltip=[
alt.Tooltip('STATE_NAME', title='state'),
alt.Tooltip('hospital_name', title='hospital name'),
alt.Tooltip('NUM_LICENSED_BEDS', title='licensed beds'),
alt.Tooltip('NUM_STAFFED_BEDS', title='staffed beds'),
alt.Tooltip('NUM_ICU_BEDS', title='icu beds'),
alt.Tooltip('BED_UTILIZATION', title='bed utilization', format='.0%'),
alt.Tooltip('hospital_type', title='hospital type'),
],
).properties(
title=f'Childrens Hospital Beds and Confirmed COVID-19 Cases by County {date.date()}'
)
(base_states + base_counties + points)
base geographic mapping of the USpoints which geographically maps the childrens hospitalsus_states = alt.topo_feature(topo_usa, 'states')
us_counties = alt.topo_feature(topo_usa, 'counties')
date = df_usa['date'].max()
# state borders
base_states = alt.Chart(us_states).mark_geoshape().encode(
stroke=alt.value('lightgray'), fill=alt.value('white')
).properties(
width=1000,
height=800,
).project(
type='albersUsa',
)
# confirmed cases by county
base_counties = alt.Chart(us_counties).mark_geoshape().encode(
color=alt.Color('confirmed:Q', scale=alt.Scale(type='log'), title='Confirmed'),
).transform_lookup(
lookup='id',
from_=alt.LookupData(df_usa[(df_usa['date'] == date) & (df_usa['confirmed'] > 0)], 'county_fips', ['confirmed'])
)
# remove the filter on `df_beds` to see all hospitals
points = alt.Chart(df_beds).mark_point(opacity=0.75).encode(
longitude='longitude:Q',
latitude='latitude:Q',
size=alt.Size('sum(NUM_ICU_BEDS):Q', title='ICU Beds'),
fill=alt.Fill('sum(bed_utilization):Q', title='Bed Utilization'),
stroke=alt.value('black'),
tooltip=[
alt.Tooltip('STATE_NAME', title='state'),
alt.Tooltip('hospital_name', title='hospital name'),
alt.Tooltip('NUM_LICENSED_BEDS', title='licensed beds'),
alt.Tooltip('NUM_STAFFED_BEDS', title='staffed beds'),
alt.Tooltip('NUM_ICU_BEDS', title='icu beds'),
alt.Tooltip('BED_UTILIZATION', title='bed utilization', format='.0%'),
alt.Tooltip('hospital_type', title='hospital type'),
],
).properties(
# update figure title
title=f'Hospital Beds and Confirmed COVID-19 Cases by County {date.date()}'
)
(base_states + base_counties + points)